﻿Imports System.Data.SqlClient


Public Class frmRMantenimientosP
    Dim Bitacora As New clsActividad
   
    Private Sub frmRMantenimientosP_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        datFecha1.Value = Now()
        RadioButton1.Checked = True

        lstA.Items.Clear()
        lstR.Items.Clear()
        cargaAutobuses()
        CargaRutas()
       
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        'Registra actividad
        Bitacora.RegistraActividad("Imprimió el reporte de Proximos Mantenimientos al dia " & datFecha1.Text)

        todos()
    End Sub
    Sub todos()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15


        Dim strFecha As String = Format(Me.datFecha1.Value.Year, "0000") & Format(Me.datFecha1.Value.Month, "00") & Format(Me.datFecha1.Value.Day, "00")
        '--------
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        strSql = "SELECT P.Fecha,P.IdAutobus,P.NoEconomico,P.IdPreventivo,P.IdServicio,P.NombreServicio,P.KmsProgramados, S.Costo, A.Importe01"
        strSql = strSql & " FROM Preventivos P INNER JOIN Servicios S ON P.IdServicio=S.IdServicio INNER JOIN Autobuses A ON P.IdAutobus=A.IdAutobus"
        'strSql = strSql & " Where CONVERT(VARCHAR,Fecha,101)<='" & strFecha & "' "
        strSql = strSql & " Order By NoEconomico,Fecha,NombreServicio"


        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim decKmsCamion As Decimal = 0
        'Dim km As Long = 0
        Dim datUltimo As Date
        rng = rng + 1
        Dim strFechaMov As String = ""
        Dim costo As Double = 0
        Dim costototal As Double = 0


        Dim difdias As String

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("Fecha").ToString).Year, "0000") & Format(CDate(rdBuscar("Fecha").ToString).Month, "00") & Format(CDate(rdBuscar("Fecha").ToString).Day, "00")
            If strFechaMov <= strFecha Then
                If strCamion <> rdBuscar("NoEconomico").ToString.Trim Then
                    If strCamion <> "?" Then
                        wb.SHEETS(1).cells(rng, 9).value = "Total Aprox"
                        wb.SHEETS(1).cells(rng, 10).value = costo
                        costo = 0
                        rng = rng + 1 ' Separa entre camiones
                    End If
                    decKmsCamion = rdBuscar("Importe01")
                    wb.SHEETS(1).cells(rng, 1).value = "Unidad: " & rdBuscar("NoEconomico").ToString.Trim
                    wb.SHEETS(1).cells(rng, 1).font.size = 12
                    wb.SHEETS(1).cells(rng, 1).font.bold = -1

                    rng = rng + 1
                    wb.SHEETS(1).cells(rng, 1).value = "Fecha entrada"
                    wb.SHEETS(1).cells(rng, 2).value = "Folio"
                    wb.SHEETS(1).cells(rng, 3).value = "Servicio"
                    wb.SHEETS(1).cells(rng, 4).value = "Tipo Servicio"
                    wb.SHEETS(1).cells(rng, 5).value = "Ultimo Servicio"
                    wb.SHEETS(1).cells(rng, 6).value = "Kms Programados"
                    wb.SHEETS(1).cells(rng, 7).value = "Kms Actuales"
                    wb.SHEETS(1).cells(rng, 8).value = "Kms Pendientes"
                    wb.SHEETS(1).cells(rng, 9).value = "Observacion"
                    wb.SHEETS(1).cells(rng, 10).value = "Costo Aprox"


                    rng = rng + 1
                End If
                strCamion = rdBuscar("NoEconomico").ToString.Trim
                datUltimo = UltimoServicio(rdBuscar("IdServicio"), CInt(rdBuscar("IdAutobus")))
                'km = KmsEntreDias(datUltimo, Now(), CInt(rdBuscar("IdAutobus")))
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Fecha")
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("IdPreventivo")
                wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NombreServicio")
                wb.SHEETS(1).cells(rng, 4).value = "Preventivo"
                wb.SHEETS(1).cells(rng, 5).value = datUltimo
                wb.SHEETS(1).cells(rng, 6).value = rdBuscar("KmsProgramados")
                wb.SHEETS(1).cells(rng, 7).value = decKmsCamion
                wb.SHEETS(1).cells(rng, 8).value = wb.SHEETS(1).cells(rng, 6).value - wb.SHEETS(1).cells(rng, 7).value
                If wb.sheets(1).cells(rng, 8).value < 0 Then
                    wb.SHEETS(1).cells(rng, 8).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 8).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 8).interior.COLORINDEX = 3
                End If
                difdias = DateDiff(DateInterval.Day, Now(), rdBuscar("Fecha"))
                If difdias < 0 Then
                    wb.SHEETS(1).cells(rng, 9).value = "Atrasado"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 3
                ElseIf difdias > 10 Then
                    wb.SHEETS(1).cells(rng, 9).value = "Proximo"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 4
                Else
                    wb.SHEETS(1).cells(rng, 9).value = "En tiempo"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 6
                End If
                wb.SHEETS(1).cells(rng, 10).value = rdBuscar("Costo") 'SetSql("sum(costoaprox) as costo from servicios s, subservicios b, serviasigna a where s.numservici=a.serviasigna and a.idsub=b.id and numservici=" & rdBuscar("numservici"))
                costo = costo + rdBuscar("Costo")
                costototal = costototal + rdBuscar("Costo")
                For i = 1 To 8
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).cells(rng, 9).value = "Total Aprox"
        wb.SHEETS(1).cells(rng, 10).value = costo


        rng = rng + 2
        wb.SHEETS(1).cells(rng, 9).value = "Total Gral"
        wb.SHEETS(1).cells(rng, 10).value = costototal

        wb.SHEETS(1).columns(1).columnwidth = 12
        wb.SHEETS(1).columns(2).columnwidth = 10
        wb.SHEETS(1).columns(3).columnwidth = 32
        wb.SHEETS(1).columns(4).columnwidth = 11
        wb.SHEETS(1).columns(5).columnwidth = 12
        wb.SHEETS(1).columns(6).columnwidth = 16
        wb.SHEETS(1).columns(7).columnwidth = 16
        wb.SHEETS(1).columns(8).columnwidth = 10
        wb.SHEETS(1).columns(9).columnwidth = 10
        wb.SHEETS(1).columns(10).columnwidth = 10
        wb.SHEETS(1).columns(10).numberformat = "#,##0.00"

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Proximos mantenimientos al " & Me.datFecha1.Value
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
    End Sub
    Sub Autobus()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15


        Dim strFecha As String = Format(Me.datFecha1.Value.Year, "0000") & Format(Me.datFecha1.Value.Month, "00") & Format(Me.datFecha1.Value.Day, "00")
        '--------
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        Dim t As Integer = lstA.Items.Count
        Dim strBUSES As String = "'"
        For i = 0 To t - 2
            strBUSES = strBUSES & Me.lstA.Items(i).ToString.Trim & "','"
        Next
        strBUSES = strBUSES & Me.lstA.Items(t - 1).ToString.Trim & "'"

        strSql = "SELECT P.Fecha,P.IdAutobus,P.NoEconomico,P.IdPreventivo,P.IdServicio,P.NombreServicio,P.KmsProgramados, S.Costo, A.Importe01"
        strSql = strSql & " FROM Preventivos P INNER JOIN Servicios S ON P.IdServicio=S.IdServicio INNER JOIN Autobuses A ON P.IdAutobus=A.IdAutobus"
        strSql = strSql & " Where NoEconomico IN (" & strBUSES & ") "
        strSql = strSql & " Order By NoEconomico,Fecha,NombreServicio"


        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim decKmsCamion As Decimal = 0
        'Dim km As Long = 0
        Dim datUltimo As Date
        rng = rng + 1
        Dim strFechaMov As String = ""
        Dim costo As Double = 0
        Dim costototal As Double = 0


        Dim difdias As String

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("Fecha").ToString).Year, "0000") & Format(CDate(rdBuscar("Fecha").ToString).Month, "00") & Format(CDate(rdBuscar("Fecha").ToString).Day, "00")
            If strFechaMov <= strFecha Then
                If strCamion <> rdBuscar("NoEconomico").ToString.Trim Then
                    If strCamion <> "?" Then
                        wb.SHEETS(1).cells(rng, 9).value = "Total Aprox"
                        wb.SHEETS(1).cells(rng, 10).value = costo
                        costo = 0
                        rng = rng + 1 ' Separa entre camiones
                    End If
                    decKmsCamion = rdBuscar("Importe01")
                    wb.SHEETS(1).cells(rng, 1).value = "Unidad: " & rdBuscar("NoEconomico").ToString.Trim
                    wb.SHEETS(1).cells(rng, 1).font.size = 12
                    wb.SHEETS(1).cells(rng, 1).font.bold = -1

                    rng = rng + 1
                    wb.SHEETS(1).cells(rng, 1).value = "Fecha entrada"
                    wb.SHEETS(1).cells(rng, 2).value = "Folio"
                    wb.SHEETS(1).cells(rng, 3).value = "Servicio"
                    wb.SHEETS(1).cells(rng, 4).value = "Tipo Servicio"
                    wb.SHEETS(1).cells(rng, 5).value = "Ultimo Servicio"
                    wb.SHEETS(1).cells(rng, 6).value = "Kms Programados"
                    wb.SHEETS(1).cells(rng, 7).value = "Kms Actuales"
                    wb.SHEETS(1).cells(rng, 8).value = "Kms Pendientes"
                    wb.SHEETS(1).cells(rng, 9).value = "Observacion"
                    wb.SHEETS(1).cells(rng, 10).value = "Costo Aprox"


                    rng = rng + 1
                End If
                strCamion = rdBuscar("NoEconomico").ToString.Trim
                datUltimo = UltimoServicio(rdBuscar("IdServicio"), CInt(rdBuscar("IdAutobus")))
                'km = KmsEntreDias(datUltimo, Now(), CInt(rdBuscar("IdAutobus")))
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Fecha")
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("IdPreventivo")
                wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NombreServicio")
                wb.SHEETS(1).cells(rng, 4).value = "Preventivo"
                wb.SHEETS(1).cells(rng, 5).value = datUltimo
                wb.SHEETS(1).cells(rng, 6).value = rdBuscar("KmsProgramados")
                wb.SHEETS(1).cells(rng, 7).value = decKmsCamion
                wb.SHEETS(1).cells(rng, 8).value = wb.SHEETS(1).cells(rng, 6).value - wb.SHEETS(1).cells(rng, 7).value
                If wb.sheets(1).cells(rng, 8).value < 0 Then
                    wb.SHEETS(1).cells(rng, 8).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 8).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 8).interior.COLORINDEX = 3
                End If
                difdias = DateDiff(DateInterval.Day, Now(), rdBuscar("Fecha"))
                If difdias < 0 Then
                    wb.SHEETS(1).cells(rng, 9).value = "Atrasado"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 3
                ElseIf difdias > 10 Then
                    wb.SHEETS(1).cells(rng, 9).value = "Proximo"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 4
                Else
                    wb.SHEETS(1).cells(rng, 9).value = "En tiempo"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 6
                End If
                wb.SHEETS(1).cells(rng, 10).value = rdBuscar("Costo") 'SetSql("sum(costoaprox) as costo from servicios s, subservicios b, serviasigna a where s.numservici=a.serviasigna and a.idsub=b.id and numservici=" & rdBuscar("numservici"))
                costo = costo + rdBuscar("Costo")
                costototal = costototal + rdBuscar("Costo")
                For i = 1 To 8
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).cells(rng, 9).value = "Total Aprox"
        wb.SHEETS(1).cells(rng, 10).value = costo


        rng = rng + 2
        wb.SHEETS(1).cells(rng, 9).value = "Total Gral"
        wb.SHEETS(1).cells(rng, 10).value = costototal

        wb.SHEETS(1).columns(1).columnwidth = 12
        wb.SHEETS(1).columns(2).columnwidth = 10
        wb.SHEETS(1).columns(3).columnwidth = 32
        wb.SHEETS(1).columns(4).columnwidth = 11
        wb.SHEETS(1).columns(5).columnwidth = 12
        wb.SHEETS(1).columns(6).columnwidth = 16
        wb.SHEETS(1).columns(7).columnwidth = 16
        wb.SHEETS(1).columns(8).columnwidth = 10
        wb.SHEETS(1).columns(9).columnwidth = 10
        wb.SHEETS(1).columns(10).columnwidth = 10
        wb.SHEETS(1).columns(10).numberformat = "#,##0.00"

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Proximos mantenimientos al " & Me.datFecha1.Value
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & UsuarioActivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
    End Sub
    Sub Ruta()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15


        Dim strFecha As String = Format(Me.datFecha1.Value.Year, "0000") & Format(Me.datFecha1.Value.Month, "00") & Format(Me.datFecha1.Value.Day, "00")
        '--------
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        Dim clasifi As Integer
        Dim t As Integer = lstR.Items.Count
        Dim strRutas As String = ""

        For i = 0 To t - 2
            clasifi = BuscarIntDatoCitra("RUTAS", "IdRuta", "Nombre='" & Me.lstR.Items(i).ToString.Trim & "'")
            strRutas = strRutas & clasifi & ","
        Next
        clasifi = BuscarIntDatoCitra("RUTAS", "IdRuta", "Nombre='" & Me.lstR.Items(t - 1).ToString.Trim & "'")
        strRutas = strRutas & clasifi



        strSql = "SELECT P.Fecha,P.IdAutobus,P.NoEconomico,P.IdPreventivo,P.IdServicio,P.NombreServicio,P.KmsProgramados, S.Costo, A.Importe01"
        strSql = strSql & " FROM Preventivos P INNER JOIN Servicios S ON P.IdServicio=S.IdServicio INNER JOIN Autobuses A ON P.IdAutobus=A.IdAutobus"
        strSql = strSql & " Where IdRuta IN (" & strRutas & ")"
        strSql = strSql & " Order By NoEconomico,Fecha,NombreServicio"


        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim decKmsCamion As Decimal = 0
        'Dim km As Long = 0
        Dim datUltimo As Date
        rng = rng + 1
        Dim strFechaMov As String = ""
        Dim costo As Double = 0
        Dim costototal As Double = 0


        Dim difdias As String

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("Fecha").ToString).Year, "0000") & Format(CDate(rdBuscar("Fecha").ToString).Month, "00") & Format(CDate(rdBuscar("Fecha").ToString).Day, "00")
            If strFechaMov <= strFecha Then
                If strCamion <> rdBuscar("NoEconomico").ToString.Trim Then
                    If strCamion <> "?" Then
                        wb.SHEETS(1).cells(rng, 9).value = "Total Aprox"
                        wb.SHEETS(1).cells(rng, 10).value = costo
                        costo = 0
                        rng = rng + 1 ' Separa entre camiones
                    End If
                    decKmsCamion = rdBuscar("Importe01")
                    wb.SHEETS(1).cells(rng, 1).value = "Unidad: " & rdBuscar("NoEconomico").ToString.Trim
                    wb.SHEETS(1).cells(rng, 1).font.size = 12
                    wb.SHEETS(1).cells(rng, 1).font.bold = -1

                    rng = rng + 1
                    wb.SHEETS(1).cells(rng, 1).value = "Fecha entrada"
                    wb.SHEETS(1).cells(rng, 2).value = "Folio"
                    wb.SHEETS(1).cells(rng, 3).value = "Servicio"
                    wb.SHEETS(1).cells(rng, 4).value = "Tipo Servicio"
                    wb.SHEETS(1).cells(rng, 5).value = "Ultimo Servicio"
                    wb.SHEETS(1).cells(rng, 6).value = "Kms Programados"
                    wb.SHEETS(1).cells(rng, 7).value = "Kms Actuales"
                    wb.SHEETS(1).cells(rng, 8).value = "Kms Pendientes"
                    wb.SHEETS(1).cells(rng, 9).value = "Observacion"
                    wb.SHEETS(1).cells(rng, 10).value = "Costo Aprox"


                    rng = rng + 1
                End If
                strCamion = rdBuscar("NoEconomico").ToString.Trim
                datUltimo = UltimoServicio(rdBuscar("IdServicio"), CInt(rdBuscar("IdAutobus")))
                'km = KmsEntreDias(datUltimo, Now(), CInt(rdBuscar("IdAutobus")))
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Fecha")
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("IdPreventivo")
                wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NombreServicio")
                wb.SHEETS(1).cells(rng, 4).value = "Preventivo"
                wb.SHEETS(1).cells(rng, 5).value = datUltimo
                wb.SHEETS(1).cells(rng, 6).value = rdBuscar("KmsProgramados")
                wb.SHEETS(1).cells(rng, 7).value = decKmsCamion
                wb.SHEETS(1).cells(rng, 8).value = wb.SHEETS(1).cells(rng, 6).value - wb.SHEETS(1).cells(rng, 7).value
                If wb.sheets(1).cells(rng, 8).value < 0 Then
                    wb.SHEETS(1).cells(rng, 8).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 8).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 8).interior.COLORINDEX = 3
                End If
                difdias = DateDiff(DateInterval.Day, Now(), rdBuscar("Fecha"))
                If difdias < 0 Then
                    wb.SHEETS(1).cells(rng, 9).value = "Atrasado"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 3
                ElseIf difdias > 10 Then
                    wb.SHEETS(1).cells(rng, 9).value = "Proximo"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 4
                Else
                    wb.SHEETS(1).cells(rng, 9).value = "En tiempo"
                    wb.SHEETS(1).cells(rng, 9).font.COLORINDEX = 2
                    wb.SHEETS(1).cells(rng, 9).FONT.BOLD = True
                    wb.SHEETS(1).cells(rng, 9).interior.COLORINDEX = 6
                End If
                wb.SHEETS(1).cells(rng, 10).value = rdBuscar("Costo") 'SetSql("sum(costoaprox) as costo from servicios s, subservicios b, serviasigna a where s.numservici=a.serviasigna and a.idsub=b.id and numservici=" & rdBuscar("numservici"))
                costo = costo + rdBuscar("Costo")
                costototal = costototal + rdBuscar("Costo")
                For i = 1 To 8
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).cells(rng, 9).value = "Total Aprox"
        wb.SHEETS(1).cells(rng, 10).value = costo


        rng = rng + 2
        wb.SHEETS(1).cells(rng, 9).value = "Total Gral"
        wb.SHEETS(1).cells(rng, 10).value = costototal

        wb.SHEETS(1).columns(1).columnwidth = 12
        wb.SHEETS(1).columns(2).columnwidth = 10
        wb.SHEETS(1).columns(3).columnwidth = 32
        wb.SHEETS(1).columns(4).columnwidth = 11
        wb.SHEETS(1).columns(5).columnwidth = 12
        wb.SHEETS(1).columns(6).columnwidth = 16
        wb.SHEETS(1).columns(7).columnwidth = 16
        wb.SHEETS(1).columns(8).columnwidth = 10
        wb.SHEETS(1).columns(9).columnwidth = 10
        wb.SHEETS(1).columns(10).columnwidth = 10
        wb.SHEETS(1).columns(10).numberformat = "#,##0.00"

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Proximos mantenimientos al " & Me.datFecha1.Value
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & UsuarioActivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
    End Sub
    Function KmsEntreDias(ByVal datFecha1 As Date, ByVal datFecha2 As Date, ByVal Unidad As Integer) As Long
        Dim kms As Long = 0
        Dim cnConn As New SqlConnection
        Dim strSql As String = ""
        cnConn.ConnectionString = CitraConnection
        cnConn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        strSql = "SELECT KilometrosTotales,Fecha FROM Diesel o "
        strSql = strSql & " Where IdAutobus=" & Unidad
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFechaMov As String
        Dim strFecha1 As String = Format(datFecha1.Year, "0000") & Format(datFecha1.Month, "00") & Format(datFecha1.Day, "00")
        Dim strFecha2 As String = Format(datFecha2.Year, "0000") & Format(datFecha2.Month, "00") & Format(datFecha2.Day, "00")

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("Fecha").ToString).Year, "0000") & Format(CDate(rdBuscar("Fecha").ToString).Month, "00") & Format(CDate(rdBuscar("Fecha").ToString).Day, "00")
            If strFechaMov >= strFecha1 And strFechaMov <= strFecha2 Then
                kms += rdBuscar("KilometrosTotales")
            End If
        Loop
        Return kms
    End Function

    Function UltimoServicio(ByVal Servicio As String, ByVal Unidad As Integer) As Date
        Dim fecha As Date = CDate("1/1/1900")
        Dim cnConn As New SqlConnection
        Dim strSql As String = ""
        cnConn.ConnectionString = CitraConnection
        cnConn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        strSql = "SELECT Fecha FROM Mantenimientos M INNER JOIN OrdenDetalleServicios O ON M.IdMantenimiento=O.IdMantenimiento"
        strSql = strSql & " Where M.IdAutobus=" & Unidad
        strSql = strSql & " And O.IdServicio=" & Servicio & ""
        strSql = strSql & " Order By Fecha"
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Do While rdBuscar.Read()
            fecha = rdBuscar("Fecha")
            Exit Do
        Loop
        Return fecha
    End Function

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()

    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

        If RadioButton1.Checked = True Then
            todos()
            Bitacora.RegistraActividad("Imprimió el reporte de mantenimientos al dia " & datFecha1.Text)
        ElseIf RadioButton2.Checked = True Then
            If lstR.Items.Count = 0 Then
                MsgBox("Debes indicar al menos una ruta", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            Ruta()
            Bitacora.RegistraActividad("Imprimió el reporte de mantenimientos al dia " & datFecha1.Text & ", Filtrado por Ruta")
        Else
            If lstA.Items.Count = 0 Then
                MsgBox("Debes indicar al menos un autobus", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            Autobus()
            Bitacora.RegistraActividad("Imprimió el reporte de mantenimientos al dia " & datFecha1.Text & ", Filtrado por Autobus")
        End If
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Close()

    End Sub
    Sub CargaRutas()
        lstRuta.Items.Clear()

        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection

            strSql = "SELECT Nombre FROM RUTAS"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                Me.lstRuta.Items.Add(rdBuscar("Nombre").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try
    End Sub
    Sub cargaAutobuses()
        lstAutobus.Items.Clear()

        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection

            strSql = "select NoEconomico from Autobuses where Estatus=1 ORDER BY NoEconomico"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader
            Dim primero As Boolean
            primero = True

            Do While rdBuscar.Read()
                Me.lstAutobus.Items.Add(rdBuscar("NoEconomico").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try

    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim aux As String = ""
        aux = lstRuta.Text.ToString.Trim
        If aux <> "" Then
            lstR.Items.Add(lstRuta.SelectedItem)
            lstRuta.Items.Remove(lstRuta.SelectedItem)
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim aux As String = ""
        aux = lstR.Text.ToString.Trim
        If aux <> "" Then
            lstRuta.Items.Add(lstR.SelectedItem)
            lstR.Items.Remove(lstR.SelectedItem)
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim aux As String = ""
        aux = lstAutobus.Text.ToString.Trim
        If aux <> "" Then
            lstA.Items.Add(lstAutobus.SelectedItem)
            lstAutobus.Items.Remove(lstAutobus.SelectedItem)
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim aux As String = ""
        aux = lstA.Text.ToString.Trim
        If aux <> "" Then
            lstAutobus.Items.Add(lstA.SelectedItem)
            lstA.Items.Remove(lstA.SelectedItem)
        End If
    End Sub

    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub

    Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = True
        lstRuta.Enabled = True
    End Sub

    Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
        lstA.Enabled = True
        lstAutobus.Enabled = True
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub
End Class